﻿using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        public SqlConnection sqlconn;
        private DataTable tb;



        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            sqlconn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            tb = new DataTable();
            dateTimePicker1.Value = Convert.ToDateTime("2014-08-01");
            dateTimePicker2.Value = DateTime.Now;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog opf = new OpenFileDialog();
                opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    string filename = opf.FileName;
                    string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                    DataSet ds = new DataSet("EXCEL");
                    OleDbConnection cn = new OleDbConnection(ConStr);

                    if (cn.State == ConnectionState.Closed) cn.Open();
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
                    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                    string select = String.Format("SELECT * FROM [{0}]", sheet1);
                    OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                    ad.Fill(ds);
                    tb.Clear();
                    tb = ds.Tables[0];
                    cn.Close();

                    if (tb.Columns.Count >= 5)
                    {
                        tb.Columns[0].ColumnName = "Код аптеки";
                        tb.Columns[1].ColumnName = "Аптека";
                        tb.Columns[2].ColumnName = "ТО розн";
                        tb.Columns[3].ColumnName = "ТО закуп";
                        tb.Columns[4].ColumnName = "Дата";
                        dataGridView1.DataSource = tb.DefaultView;
                    }
                    else
                    {
                        MessageBox.Show("Недостаточно столбцов в файле\nНужны столбцы: Код аптеки,Аптека,ТО розн,ТО закуп,Дата");
                        tb.Clear();
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (tb.Rows.Count > 0)
            {
                SqlStorProc.sp_params[] sp = new SqlStorProc.sp_params[4];
                sp[0].name = "@pharmcode";
                sp[1].name = "@date";
                sp[2].name = "@sales_ret";
                sp[3].name = "@sales_pur";
                try
                {
                    int i = 0;
                    foreach (DataRow row in tb.Rows)
                    {
                        if (row[0] != null)
                            if (row[4] != null)
                                if (row[2] != null && row[3] != null)
                                {
                                    sp[0].value = row[0];
                                    sp[1].value = row[4];
                                    sp[2].value = row[2];
                                    sp[3].value = row[3];
                                    new SqlStorProc().SqlProcExecNonQuery(sqlconn, "pas_DocData366Ins", 30, sp);
                                    i++;
                                }
                    }
                    MessageBox.Show("Загружено " + i.ToString() + " строк");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Есть ошибки!\n" + ex.Message);
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[2];
            par[0].name = "@date1";
            par[0].value = dateTimePicker1.Value.ToString("yyyyMMdd");
            par[1].name = "@date2";
            par[1].value = dateTimePicker2.Value.ToString("yyyyMMdd");

            DataTable dt366 = new SqlStorProc().SqlProcExecWithQueryOneTable(sqlconn, "pas_Docs366Sel", 30, par);

            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "Продажи 36и6";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                new ExpToExcel().SaveDGVToCSVfile(sfd.FileName.ToString(), dt366, true);
            }
        }
    }
}
